import socket
import time
import pymysql
import threading
from datetime import datetime

# TCP服务器配置
host = '192.168.0.168'  # 监听所有接口
port = 8080     # 监听的端口

# MySQL数据库1配置
mysql_host = 'localhost'
mysql_user = 'root'
mysql_password = '123456'
mysql_db = 'iot_local'

# MySQL数据库2配置
mysql_host2 = 'localhost'
mysql_user2 = 'root'
mysql_password2 = '123456'
mysql_db2 = 'ryerp'


def client_handler(client_socket, client_address):
    # 连接到MySQL数据库
    try:
        mysql_conn = pymysql.connect(host=mysql_host, user=mysql_user, password=mysql_password, db=mysql_db)
        mysql_cursor = mysql_conn.cursor()
        mysql_conn2 = pymysql.connect(host=mysql_host2, user=mysql_user2, password=mysql_password2, db=mysql_db2)
        mysql_cursor2 = mysql_conn2.cursor()
        while True:
            # 接收数据
            data = client_socket.recv(1024).decode('utf-8')
            if not data:
                break  # 如果没有数据，跳出循环
            # 解析数据
            data_lines = data.strip().split(';')
            for line in data_lines:
                if line:
                    parts = line.split(',')
                    if len(parts) == 4:  # Ensure there are 4 parts to unpack
                        grp, dev, type_, num = parts
                        grp, dev, type_, num = grp.split(':')[1], dev.split(':')[1], type_.split(':')[1], \
                                               num.split(':')[1]
                        if type_ == '2':
                            if int(dev) > 252:
                                sql = 'UPDATE mm_new_device_vision_2 SET status = 1 WHERE device_id = %s'
                            else:
                                sql = 'UPDATE mm_new_device_vision SET status = 1 WHERE device_id = %s'
                            mysql_cursor2.execute(sql, (dev,))
                            mysql_conn2.commit()
                            # 尝试发送数据，处理可能的连接拒绝情况
                            send_success = False
                            max_retries = 3  # 最大重连次数
                            retries = 0
                            while not send_success and retries < max_retries:
                                try:
                                    client_socket.sendall(line.encode('utf-8'))
                                    send_success = True
                                except socket.error as e:
                                    print(f"Send failed, retrying... Error: {e}")
                                    # 这里可以添加一些适当的等待时间后再重试
                                    time.sleep(1)
                                    retries += 1
                                    if retries < max_retries:
                                        # 尝试重新建立连接（这里简单关闭再重新连接示例，具体可能需根据实际情况调整）
                                        client_socket.close()
                                        client_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
                                        client_socket.connect((client_address[0], client_address[1]))
                                    else:
                                        print(f"Failed to send data after {max_retries} retries.")
                            # print('reply:' + line)
                        elif type_ == '3':
                            if int(dev) > 252:
                                sql = 'UPDATE mm_new_device_vision_2 SET status = 0 WHERE device_id = %s'
                            else:
                                sql = 'UPDATE mm_new_device_vision SET status = 0 WHERE device_id = %s'
                            mysql_cursor2.execute(sql, (dev,))
                            mysql_conn2.commit()
                            send_success = False
                            max_retries = 3
                            retries = 0
                            while not send_success and retries < max_retries:
                                try:
                                    client_socket.sendall(line.encode('utf-8'))
                                    send_success = True
                                except socket.error as e:
                                    print(f"Send failed, retrying... Error: {e}")
                                    time.sleep(1)
                                    retries += 1
                                    if retries < max_retries:
                                        client_socket.close()
                                        client_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
                                        client_socket.connect((client_address[0], client_address[1]))
                                    else:
                                        print(f"Failed to send data after {max_retries} retries.")
                        #     print('reply:' + line)
                        elif type_ == '1' or type_ == '0':
                            print(grp, dev, type_, num)

                    else:
                        print(f"Skipping invalid line: {line}")
                        continue
                    receive_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

                    # 写入MySQL数据库
                    sql = "INSERT INTO iotdemo (grp, dev, type, num, receive_time) VALUES (%s, %s, %s, %s, %s)"
                    mysql_cursor.execute(sql, (grp, dev, type_, num, receive_time))
                    mysql_conn.commit()
    except Exception as e:
        print(f"Error handling client {client_address}: {type(e).__name__}: {e}")
        print(f"Client connection timed out {client_address}")
        mysql_cursor.close()
        mysql_conn.close()
        mysql_cursor2.close()
        mysql_conn2.close()
    finally:
        # 关闭客户端连接
        client_socket.close()
        print(f"Connection with {client_address} closed")


def start_server():
    # 创建TCP服务器套接字
    server_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    server_socket.bind((host, port))
    server_socket.listen(128)
    print(f"TCP Server listening on {host}:{port}")

    try:
        while True:
            # 等待客户端连接
            client_socket, addr = server_socket.accept()
            print(f"New connection from {addr}")
            client_thread = threading.Thread(target=client_handler, args=(client_socket, addr))
            client_thread.start()
    except Exception as e:
        print(f"Server error: {type(e).__name__}: {e}")
        time.sleep(5)  # 暂停5秒
        start_server()  # 重启服务器
    finally:
        # 关闭服务器套接字
        server_socket.close()


if __name__ == "__main__":
    try:
        start_server()
    except KeyboardInterrupt:
        print("TCP Server stopped by keyboard interrupt")